# # from django.test import TestCase
#
# import os
# import sys
# # from datetime import date
#
# import django
# # from django.db.models import F, Q, Avg, Count, Max, Min
#
# # sys.path.insert(0, 'D:\每月了解\第七个月\实验楼项目仓库\syl_1912\syl')
# sys.path.insert(0, 'D:\每月了解\第七个月\实验楼项目仓库\syl_1912\syl')
#
# os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'syl.settings')
# django.setup()
#
# from demo_app.models import BookInfo, HeroInfo
#
# # 简单查询
# book = BookInfo.objects.get(id=1)
# print(book)

import os
import django
import sys

sys.path.insert(0, 'D:\每月了解\第七个月\实验楼项目仓库\syl_1912\syl')
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "syl.settings")
django.setup()
from demo_app.models import BookInfo

# 简单查询
book = BookInfo.objects.get(id=1)
print(book)

# 增加数据
# book = BookInfo(btitle='新书', bpub_date='2002-09-09')
# book.save()
# book = BookInfo(btitle='新书2', bpub_date=date(1988,1,1))
# print(book)
# book.save()
# print(book)
# book = BookInfo.objects.create(btitle='新书3', bpub_date=date(1988,1,1))
# print(book)

# book = BookInfo.objects.get(id=5)
# book.delete()

# books = BookInfo.objects.filter(id__gt=4)
# books = BookInfo.objects.filter(id__in=[6,7])
# books = BookInfo.objects.filter(btitle__contains='新书',bpub_date='1988-01-01')
# books = BookInfo.objects.filter(bpub_date='1988-01-01')
# books = BookInfo.objects.filter(bread=0)
# books = BookInfo.objects.filter(bcomment=0)
# books.delete()


# 修改
# 1. 单条
# book = BookInfo.objects.get(id=8)
# book.btitle = '新书'
# book.save()
# books = BookInfo.objects.filter(btitle__contains='新书')
# books.update(is_delete=True)  # 所有的列表中的书, 被更新的字段, 都是同一个值

# 查询
# 1. get  获取一个   2. 获取多个,  all,   filter,   exclude
# try:
#     book = BookInfo.objects.get(id=0)  # 有且只有一个结果的时候, 才能成功
# except:
#     book = None
# books = BookInfo.objects.filter(id=0)
# print(books)
# book = books.first()  # books.last()
# print(book)
# books = BookInfo.objects.all()  # 所有的
# books.first()
# books= BookInfo.objects.exclude(id=1)
# print(books)

# 查询书名包含'传'的图书。
# BookInfo.objects.filter(btitle__contains='传')
# 查询书名以'部'结尾的图书
# BookInfo.objects.filter(btitle__startswith='天')
# BookInfo.objects.filter(btitle__endswith='部')
# books= BookInfo.objects.filter(btitle__search='八')
# print(books)

# heros = HeroInfo.objects.filter(hcomment=None)
# heros = HeroInfo.objects.filter(hcomment__isnull=True)
# for hero in heros:
#     print(hero.id)

# books = BookInfo.objects.filter(bread__in=[12, 36, 20])
# books = BookInfo.objects.filter(bread__in=[0,1,2,3,4,5....50 36, 20])
# books = BookInfo.objects.filter(bread__range=[0, 50])
# print(books)

# books = BookInfo.objects.filter(bread__gt=0)
# books = BookInfo.objects.filter(bread__gte=0)
# books = BookInfo.objects.filter(bread__lt=0)
# books = BookInfo.objects.filter(bread__lte=0)
# print(books)

# books = BookInfo.objects.filter(bpub_date__year__gt='1990')
# books = BookInfo.objects.filter(bpub_date__month__gt='10')
# books = BookInfo.objects.filter(bpub_date__day__gt='20')
# books = BookInfo.objects.filter(bpub_date__week_day='7') # 每周的第一天, 周日
# week_day、hour、minute、second
# bpub_date : 年月日    bpub_date__year  年    __gt 大于
# print(books)

# 例：查询阅读量大于等于评论量的图书。
# books = BookInfo.objects.exclude(bread=0).filter(bread__gte=F('bcomment'))  # 一个对象的两个字段之间进行比较  F('另外一个字段名')
# print(books)
# books = BookInfo.objects.exclude(bread=0).filter(bread__gte=F('bcomment') * 2)  # 一个对象的两个字段之间进行比较  F('另外一个字段名')
# print(books)
# 例：查询阅读量大于2倍评论量的图书。

# 例：查询阅读量大于20，并且编号小于3的图书。
# print(BookInfo.objects.filter(bread__gt=20, id__lt=3))

# 例：查询阅读量大于20，或编号小于3的图书。
# Q  =>  查询
# print(BookInfo.objects.filter(Q(bread__gt=20) | Q(id__lt=3)))  # | 或,   & 且,  ! 非
# print(BookInfo.objects.filter(Q(bread__gt=20) | ~Q(id__lt=3)))  # | 或,   & 且,  ~ 非


# 例：查询图书的总阅读量。
# books = BookInfo.objects.all()  #  1. 从数据库中取了所有的书的数据(取回的数据量大)
# bread_count = 0
# for book in books:   # 2. 累加,  需要遍历数据
#     bread_count += book.bread
#
# print(bread_count)
#
# from django.db.models import Sum
#
# result = BookInfo.objects.aggregate(Sum('bcomment'),Sum('bread'),Avg('bread'), Count('bread'), Max('bread'),Min('bread') )   # 1. 数据库给累加的  快速高效
# print(result)

# 聚合函数包括：Avg 平均，Count 数量，Max 最大，Min 最小，Sum 求和
# {'bread__sum': 136, 'bread__avg': 22.6667, 'bread__count': 6, 'bread__max': 58, 'bread__min': 0}
# books = BookInfo.objects.all().order_by('bread')  # 升序
# books = BookInfo.objects.all().order_by('-bread')  # 降序
# print(books)
# 2020-11-11
# 2020-11-12

# 所属关系,   英雄是属于书的,
# 关联查询,   两方,   从一方查另外一方, 关联查询

# book_1 = BookInfo.objects.get(id=1)

# hero = HeroInfo.objects.get(id=1)
# book = hero.hbook # 英雄所在书籍的对象
# print(book.id)
# print(book.btitle)

# book = BookInfo.objects.get(id=1)
# heros = book.heros.all()
# print(heros)

# 过滤查询, 条件是 关联模型 的属性

# heros= HeroInfo.objects.filter(hbook__btitle__contains='传')
# print(heros)
books = BookInfo.objects.filter(heros__hcomment__contains='掌')
print(books.distinct())

